﻿/********************************************************************************************************************
* Author:	Christos Polydorou																						*
* Email:	std04237@di.uoa.gr																						*
* Purpose:	This script is used in order to create the stored procedure "usp_category_changeowner" that changes the	*
*			owner of a category.																					*
********************************************************************************************************************/

CREATE PROCEDURE [dbo].[usp_category_changeowner]
	@currowner nvarchar(50),
	@catname nvarchar(50),
	@newowner nvarchar(50)
AS
	DECLARE @ownerID bigint
	DECLARE @newOwnerID bigint
		
	SELECT @ownerID = users.ID						/* get the id of the current owner */
	FROM Users
	WHERE Users.Username = @currowner
	
	SELECT @newOwnerID = users.ID					/* get the id of the new owner */
	FROM Users
	WHERE Users.Username = @newowner

	UPDATE Categories								/* update the category */
	SET Owner = @newOwnerID
	WHERE Categories.Name = @catname and Categories.Owner = @OwnerID

RETURN 0